package com.wms.auto;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

public class CreateFile {
	
	private static String driver = "com.mysql.jdbc.Driver";
	private static String url = "jdbc:mysql://127.0.0.1:3306/wms";
	private static String schema = null;
	private static String catalog = null;
	private static String uid = "root";
	private static String pwd = "root";
	
	public static String PROP_COLUMN_TYPE = "columnType";
	public static String PROP_COLUMN_NAME = "columnName";
	public static String PROP_COLUMN_NAME_LABEL = "columnNameLabel";
	
	private Connection cn = null;
	private List<String> fieldTypes = null;
	private List<String> executedTables = null;
	private String[] types = {"TABLE", "VIEW"}; //只生成表和视图的数据字典
	
	public CreateFile() {

		try {
			
			initData();
			Class.forName(driver);
			this.cn = DriverManager.getConnection(url, uid, pwd);
		} 
		catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		
		try {
			
			CreateFile cf = new CreateFile();
			CreatePojo cp = new CreatePojo();
			CreateDao ca = new CreateDao();
			CreateService cs = new CreateService();
			CreateServiceImpl csi = new CreateServiceImpl();
			CreateDaoImpl cdi = new CreateDaoImpl();
			
			List<String> lstTableName = cf.readTableList();
			
			List<Map<String, String>> lstTableColumn = new ArrayList<Map<String, String>>();
			
			for (String strTableName : lstTableName) {
				
				lstTableColumn = cf.readTable(strTableName);
				
				System.out.println("===================" + strTableName);
				
//				cp.create(strTableName, lstTableColumn);
//				ca.create(strTableName);
//				cs.create(strTableName);
				cdi.create(strTableName, lstTableColumn);
//				csi.create(strTableName);
			}
			
		} catch (Exception e) {
			
			e.printStackTrace();
		}
	}
	
	public List<Map<String, String>> readTable(String strTableName) throws SQLException {
		
		Map<String, String> mapTableInfo = new HashMap<String, String>();
		List<Map<String, String>> lstTableColumn = new ArrayList<Map<String, String>>();
		
		Statement stm = cn.createStatement();
		ResultSet rsColumn = stm.executeQuery("select * from " + strTableName);
		ResultSetMetaData rsmd = rsColumn.getMetaData();
		
		for (int i = 1; i <= rsmd.getColumnCount(); i++) {
			
			mapTableInfo = new HashMap<String, String>();
			
			// 字段类型
			String columnType = rsmd.getColumnTypeName(i);
			
			// 字段名称
			String columnName = rsmd.getColumnName(i);
			
			// 字段注释
			String columnNameLabel = rsmd.getColumnLabel(i);
			
			if (StringUtils.equalsIgnoreCase("id", columnName)
					|| StringUtils.equalsIgnoreCase("remarks", columnName)
					|| StringUtils.equalsIgnoreCase("create_date", columnName)
					|| StringUtils.equalsIgnoreCase("create_by", columnName)
					|| StringUtils.equalsIgnoreCase("update_date", columnName)
					|| StringUtils.equalsIgnoreCase("update_by", columnName)
					) {
				
				continue;
			}
			
			mapTableInfo.put(PROP_COLUMN_NAME, columnName);
			mapTableInfo.put(PROP_COLUMN_TYPE, columnType);
			mapTableInfo.put(PROP_COLUMN_NAME_LABEL, columnNameLabel);
			
			lstTableColumn.add(mapTableInfo);
		}
		
		return lstTableColumn;
	}
	
	public List<Map<String, String>> readTable2(String strTableName) throws SQLException {
		
		Map<String, String> mapTableInfo = new HashMap<String, String>();
		List<Map<String, String>> lstTableColumn = new ArrayList<Map<String, String>>();
		
		PreparedStatement ps = null;
		ps = cn.prepareStatement(this.getSqlStr(strTableName));
		ResultSet rs = ps.executeQuery();
		
		while (rs.next()) {
			
			mapTableInfo = new HashMap<String, String>();
			
			String columnName = rs.getString(1);
			String columnType = rs.getString(2);
			String columnNameLabel = rs.getString(3);
			
			if (StringUtils.equalsIgnoreCase("id", columnName)
					|| StringUtils.equalsIgnoreCase("remarks", columnName)
					|| StringUtils.equalsIgnoreCase("create_date", columnName)
					|| StringUtils.equalsIgnoreCase("create_by", columnName)
					|| StringUtils.equalsIgnoreCase("update_date", columnName)
					|| StringUtils.equalsIgnoreCase("update_by", columnName)
					) {
				
				continue;
			}
			
			mapTableInfo.put(PROP_COLUMN_NAME, columnName);
			mapTableInfo.put(PROP_COLUMN_TYPE, columnType);
			mapTableInfo.put(PROP_COLUMN_NAME_LABEL, columnNameLabel);
			
			lstTableColumn.add(mapTableInfo);
		}
		
		return lstTableColumn;
	}
	
	/**
	 * 取得一个表的所有主键字段
	 */
	private String getSqlStr(String tableName) {

		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT A.COLUMN_NAME    字段名, ");
		sql.append("        A.DATA_TYPE      数据类型, ");
		sql.append("        B.COMMENTS       备注 ");
		sql.append("   FROM USER_TAB_COLUMNS A, ");
		sql.append("        USER_COL_COMMENTS B, ");
		sql.append("        (SELECT COUNT(*) INDEXCOUNT, COLUMN_NAME ");
		sql.append("           FROM USER_IND_COLUMNS ");
		sql.append("          WHERE TABLE_NAME = '" + tableName + "' ");
		sql.append("          GROUP BY COLUMN_NAME) C ");
		sql.append("  WHERE A.TABLE_NAME = B.TABLE_NAME ");
		sql.append("    AND A.COLUMN_NAME = B.COLUMN_NAME ");
		sql.append("    AND A.COLUMN_NAME = C.COLUMN_NAME(+) ");
		sql.append("    AND A.TABLE_NAME = '" + tableName + "' ");
		
		System.out.println(sql.toString());
		
		return sql.toString();
	}
	
	/**
	 * 取得一个表的所有主键字段
	 */
	private String getTablePrimaryKeys(String tableName) {

		try {
			
			DatabaseMetaData dbmd = cn.getMetaData();
			ResultSet rs = dbmd.getPrimaryKeys(catalog, schema, tableName);
			StringBuffer sb = new StringBuffer(",");
			
			while (rs.next()) {
				sb.append(rs.getString("COLUMN_NAME") + ",");
			}
			
			rs.close();

			return sb.toString();
		} 
		catch (Exception ex) {
			return "";
		}
	}
	
	public List<String> readTableList() throws SQLException {
		
		List<String> lstTable = new ArrayList<String>();
		
		DatabaseMetaData dbmd = cn.getMetaData();
		ResultSet rs = dbmd.getTables(catalog, schema, "%", types);
		int tableCount = 0;
		
		while (rs.next()) {
			
			String tableName = rs.getString("TABLE_NAME");
			
			// 排除表
			if (executedTables.contains(tableName.toLowerCase())) {
				
				continue;
			}
			
			lstTable.add(tableName);
			
			tableCount++;
		}
		
		return lstTable;
	}
	
	private void initData() {

		// 字符串类型
		fieldTypes = new ArrayList();
		fieldTypes.add("CHAR");
		fieldTypes.add("NCHAR");
		fieldTypes.add("VARCHAR");
		fieldTypes.add("NVARCHAR");
		fieldTypes.add("VARCHAR2");
		fieldTypes.add("NVARCHAR2");

		// 排除以下表
		executedTables = new ArrayList();
		executedTables.add("dtproperties");
		executedTables.add("sysconstraints");
		executedTables.add("syssegments");
	}
}